package com.zy.excel.test2;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.List;

/**
 * 把excel数据导入
 */
public class ImportExcelUtil {

    /**
     * 根据文件信息读取xsl文件解析为List<List<String>>
     * @param file 文件
     * @return
     * @throws Exception
     */
    public static List<List<String>> importExcelUtil(File file) throws Exception {

        FileInputStream inputStream = new FileInputStream(file);
        Workbook wb = new HSSFWorkbook(inputStream);

        //获取第1个表单
        Sheet sheet = wb.getSheetAt(0);

        //获取第一行和最后一行
        int firstRow = sheet.getFirstRowNum();
        int lastRow = sheet.getLastRowNum();

        List<List<String>> lists = new ArrayList<>();
        List<String> list = new ArrayList<>();

        //获取第0行的数据
        Row row = sheet.getRow(0);
        int firstCell = row.getFirstCellNum();
        int lastCell = row.getLastCellNum();
        for (int i = firstCell; i < lastCell; i++) {
            list.add(row.getCell(i).toString());
        }
        lists.add(list);

        //获取每行的数据
        for (int i = firstRow+1; i <= lastRow; i++) {
            List<String> list1 = new ArrayList();
            Row row_ = sheet.getRow(i);
            int firstCell_ =  row.getFirstCellNum();
            int lastCell_ =  row.getLastCellNum();
            for (int j = firstCell_; j < lastCell_; j++) {
                list1.add(row_.getCell(j).toString());
            }
            lists.add(list1);
        }

        return lists;
    }
}
